package com.example.myapplication.data.database

import androidx.room.*
import kotlinx.coroutines.flow.Flow

/**
 * 储物物品数据访问对象
 */
@Dao
interface StorageItemDao {
    
    /**
     * 根据储物空间ID获取所有物品
     */
    @Query("SELECT * FROM storage_items WHERE space_id = :spaceId ORDER BY name ASC")
    fun getItemsForSpace(spaceId: String): Flow<List<StorageItemEntity>>
    
    /**
     * 根据ID获取物品
     */
    @Query("SELECT * FROM storage_items WHERE id = :id")
    suspend fun getItemById(id: String): StorageItemEntity?
    
    /**
     * 根据ID获取物品（Flow版本）
     */
    @Query("SELECT * FROM storage_items WHERE id = :id")
    fun getItemByIdFlow(id: String): Flow<StorageItemEntity?>
    
    /**
     * 获取所有物品
     */
    @Query("SELECT * FROM storage_items ORDER BY name ASC")
    fun getAllItems(): Flow<List<StorageItemEntity>>
    
    /**
     * 搜索物品
     */
    @Query("""
        SELECT * FROM storage_items 
        WHERE name LIKE '%' || :query || '%' 
        OR description LIKE '%' || :query || '%'
        OR tags LIKE '%' || :query || '%'
        ORDER BY name ASC
    """)
    fun searchItems(query: String): Flow<List<StorageItemEntity>>
    
    /**
     * 根据标签搜索物品
     */
    @Query("""
        SELECT * FROM storage_items 
        WHERE tags LIKE '%' || :tag || '%'
        ORDER BY name ASC
    """)
    fun getItemsByTag(tag: String): Flow<List<StorageItemEntity>>
    
    /**
     * 获取指定数量范围的物品
     */
    @Query("""
        SELECT * FROM storage_items 
        WHERE quantity BETWEEN :minQuantity AND :maxQuantity
        ORDER BY name ASC
    """)
    fun getItemsByQuantityRange(minQuantity: Int, maxQuantity: Int): Flow<List<StorageItemEntity>>
    
    /**
     * 插入物品
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertItem(item: StorageItemEntity): Long
    
    /**
     * 批量插入物品
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertItems(items: List<StorageItemEntity>)
    
    /**
     * 更新物品
     */
    @Update
    suspend fun updateItem(item: StorageItemEntity)
    
    /**
     * 删除物品
     */
    @Delete
    suspend fun deleteItem(item: StorageItemEntity)
    
    /**
     * 根据ID删除物品
     */
    @Query("DELETE FROM storage_items WHERE id = :id")
    suspend fun deleteItemById(id: String)
    
    /**
     * 迁移物品到新的储物空间
     */
    @Query("UPDATE storage_items SET space_id = :newSpaceId, updated_at = :updatedAt WHERE id = :itemId")
    suspend fun migrateItem(itemId: String, newSpaceId: String, updatedAt: Long = System.currentTimeMillis())
    
    /**
     * 批量迁移物品
     */
    @Query("UPDATE storage_items SET space_id = :newSpaceId, updated_at = :updatedAt WHERE id IN (:itemIds)")
    suspend fun migrateItems(itemIds: List<String>, newSpaceId: String, updatedAt: Long = System.currentTimeMillis())
    
    /**
     * 获取储物空间中的物品总数
     */
    @Query("SELECT COUNT(*) FROM storage_items WHERE space_id = :spaceId")
    suspend fun getItemCountBySpaceId(spaceId: String): Int
    
    /**
     * 获取所有物品的总数量
     */
    @Query("SELECT SUM(quantity) FROM storage_items WHERE space_id = :spaceId")
    suspend fun getTotalQuantityBySpaceId(spaceId: String): Int?
}